﻿Imports System.Data.OleDb

Public Class ContactServiceImpl
    Implements ContactService

    Const DEFAULT_CONN_STRING As String = "provider=Microsoft.ACE.OLEDB.12.0;;data source = contacts.accdb"
    Dim conn As OleDb.OleDbConnection

    Dim sequencer As SequenceFactory

    Public Sub New()
        Me.New(DEFAULT_CONN_STRING)
    End Sub

    Sub New(ByVal conn_string As String)
        Me.conn = New OleDb.OleDbConnection(conn_string)
        conn.Open()
        Me.sequencer = New SequenceFactoryImpl(conn)
    End Sub

    Public Function getConn() As OleDb.OleDbConnection
        Return Me.conn
    End Function

    Public Function saveContact(ByRef contact As Contact) As Boolean Implements ContactService.saveContact

        If (contact.isNew()) Then
            addContact(contact)
        Else
            updateContact(contact)
        End If

        saveEmails(contact)
        saveAddresses(contact)
        savePhoneNumbers(contact)


        Return True

    End Function

    Public Function deleteContact(id As Long) As Boolean Implements ContactService.deleteContact

        deleteContactDetails(Tables.email, id)
        deleteContactDetails(Tables.address, id)
        deleteContactDetails(Tables.phone, id)

        Dim commandText = "delete from contacts where id = ?;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("id", OleDbType.Numeric).Value = id
        Return command.ExecuteNonQuery() > 0

    End Function

    Public Function getContactById(id As Long) As Contact Implements ContactService.getContactById

        Dim commandText As String = "select id, first_name, last_name, title, company, display_name from contacts where id = ?;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("id", OleDbType.Numeric).Value = id

        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        If Not reader.HasRows Then
            Return Nothing
        End If

        reader.Read()
        Dim contact As Contact = mapDBRowToContact(reader)
        populateEmails(contact)
        populatePhones(contact)
        populateAddresses(contact)

        Return contact

    End Function

    Private Function saveEmail(email As Email, contactId As Long) As Boolean

        If (email.isNew()) Then
            Return addEmail(email, contactId)
        Else
            Return updateEmail(email)
        End If

    End Function

    Private Function addEmail(ByRef email As Email, contactId As Long) As Boolean

        Dim commandText As String = "INSERT INTO email (id, contact_id, address, display_name) values(?, ?, ?, ?);"
        Dim command As New OleDbCommand(commandText, conn)
        email.id = sequencer.nextIdFor(Tables.email)
        command.Parameters.Add("id", OleDbType.Numeric).Value = email.id
        command.Parameters.Add("contact_id", OleDbType.Numeric).Value = contactId
        command.Parameters.Add("address", OleDbType.VarChar, 50).Value = email.address
        command.Parameters.Add("display_name", OleDbType.VarChar, 20).Value = email.displayName
        Dim isSuccess = command.ExecuteNonQuery() > 0
        Return isSuccess

    End Function

    Private Function updateEmail(email As Email) As Boolean

        Dim commandText As String = "UPDATE email set address = ?, display_name = ? where id = ?;"
        Dim command As New OleDbCommand(commandText, conn)
        command.Parameters.Add("address", OleDbType.VarChar, 50).Value = email.address
        command.Parameters.Add("display_name", OleDbType.VarChar, 20).Value = email.displayName
        command.Parameters.Add("id", OleDbType.Numeric).Value = email.id
        Dim isSuccess = command.ExecuteNonQuery() > 0
        Return isSuccess

    End Function

    Private Sub addContact(ByRef contact As Contact)

        contact.id = sequencer.nextIdFor(Tables.contacts)
        Dim commandText As String = "INSERT INTO contacts (id, first_name, last_name, company, title, display_name) values(?, ?, ?, ?, ?, ?);"
        Dim command As New OleDbCommand(commandText, conn)
        command.Parameters.Add("id", OleDbType.Numeric).Value = contact.id
        command.Parameters.Add("first_name", OleDbType.VarChar, 20).Value = contact.firstName
        command.Parameters.Add("last_name", OleDbType.VarChar, 20).Value = contact.lastName
        command.Parameters.Add("company", OleDbType.VarChar, 20).Value = contact.company
        command.Parameters.Add("title", OleDbType.VarChar, 20).Value = contact.title
        command.Parameters.Add("display_name", OleDbType.VarChar, 20).Value = contact.displayName
        Dim isSuccess = command.ExecuteNonQuery() > 0

    End Sub

    Private Function updateContact(contact As Contact) As Boolean

        Dim commandText As String = "UPDATE contacts set first_name = ?, last_name = ?, company = ?, title = ?, display_name = ? where id = ?;"
        Dim command As New OleDbCommand(commandText, conn)
        command.Parameters.Add("first_name", OleDbType.VarChar, 20).Value = contact.firstName
        command.Parameters.Add("last_name", OleDbType.VarChar, 20).Value = contact.lastName
        command.Parameters.Add("company", OleDbType.VarChar, 20).Value = contact.company
        command.Parameters.Add("title", OleDbType.VarChar, 20).Value = contact.title
        command.Parameters.Add("display_name", OleDbType.VarChar, 20).Value = contact.displayName
        command.Parameters.Add("id", OleDbType.Numeric).Value = contact.id
        Return command.ExecuteNonQuery() > 0

    End Function

    Private Sub saveEmails(contact As Contact)

        For Each email As Email In contact.emailList
            saveEmail(email, contact.id)
        Next

        deleteEntities(Tables.email, contact.emailIds, contact.id)
    End Sub

    Private Sub saveAddresses(contact As Contact)
        For Each address As Address In contact.addressList
            saveAddress(address, contact.id)
        Next

        deleteEntities(Tables.address, contact.addressIds, contact.id)

    End Sub

    Private Sub savePhoneNumbers(ByRef contact As Contact)

        For Each phone As PhoneNumber In contact.phoneList
            savePhoneNumber(phone, contact.id)
        Next

        deleteEntities(Tables.phone, contact.phoneIds, contact.id)

    End Sub

#Region "IDisposable Support"

    Private isDisposed As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not Me.isDisposed Then
            If disposing Then
                If Not (IsNothing(conn)) Then
                    Console.Write("service disposing")
                    conn.Close()
                    conn.Dispose()
                End If
            End If
        End If
        Me.isDisposed = True
    End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

    Private Function savePhoneNumber(ByRef phone As PhoneNumber, contactId As Long) As Boolean
        If (phone.isNew()) Then
            Return addPhoneNumber(phone, contactId)
        Else
            Return updatePhoneNumber(phone)
        End If
    End Function

    Private Function addPhoneNumber(ByRef phone As PhoneNumber, contactId As Long) As Boolean

        phone.id = sequencer.nextIdFor(Tables.phone)
        Dim commandText As String = "INSERT INTO phone (id, contact_id, area_code, prefix, line_number, ext, type_cd) values(?, ?, ?, ?, ?, ?, ?);"
        Dim command As New OleDbCommand(commandText, conn)
        command.Parameters.Add("id", OleDbType.Numeric).Value = phone.id
        command.Parameters.Add("contact_id", OleDbType.VarChar, 20).Value = contactId
        command.Parameters.Add("area_code", OleDbType.VarChar, 20).Value = phone.areaCode
        command.Parameters.Add("prefix", OleDbType.VarChar, 20).Value = phone.prefix
        command.Parameters.Add("line_number", OleDbType.VarChar, 20).Value = phone.lineNumber
        command.Parameters.Add("ext", OleDbType.VarChar, 20).Value = phone.ext
        command.Parameters.Add("type", OleDbType.VarChar, 20).Value = phone.type
        Return command.ExecuteNonQuery() > 0

    End Function

    Private Function updatePhoneNumber(phone As PhoneNumber) As Boolean

        Dim commandText As String = "UPDATE phone set area_code = ?, prefix = ?, line_number = ?, ext = ?, type_cd = ? where id = ?;"
        Dim command As New OleDbCommand(commandText, conn)
        command.Parameters.Add("area_code", OleDbType.VarChar, 20).Value = phone.areaCode
        command.Parameters.Add("prefix", OleDbType.VarChar, 20).Value = phone.prefix
        command.Parameters.Add("line_number", OleDbType.VarChar, 20).Value = phone.lineNumber
        command.Parameters.Add("ext", OleDbType.VarChar, 20).Value = phone.ext
        command.Parameters.Add("type_cd", OleDbType.VarChar, 20).Value = phone.type
        command.Parameters.Add("id", OleDbType.Numeric).Value = phone.id
        Return command.ExecuteNonQuery() > 0

    End Function

    Private Function saveAddress(ByRef address As Address, id As Long) As Boolean
        If (address.isNew()) Then
            Return addAddress(address, id)
        Else
            Return updateAddress(address)
        End If
    End Function

    Private Function addAddress(ByRef addr As Address, contactId As Long) As Boolean

        Dim commandText As String = "INSERT INTO address (id, contact_id, street_1, street_2, city, state, zip, type_cd) values(?, ?, ?, ?, ?, ?, ?, ?);"
        Dim command As New OleDbCommand(commandText, conn)
        addr.id = sequencer.nextIdFor(Tables.address)
        command.Parameters.Add("id", OleDbType.Numeric).Value = addr.id
        command.Parameters.Add("contact_id", OleDbType.VarChar, 20).Value = contactId
        command.Parameters.Add("street_1", OleDbType.VarChar, 20).Value = addr.street1
        command.Parameters.Add("street_2", OleDbType.VarChar, 20).Value = addr.street2
        command.Parameters.Add("city", OleDbType.VarChar, 20).Value = addr.city
        command.Parameters.Add("state", OleDbType.VarChar, 20).Value = addr.state
        command.Parameters.Add("zip", OleDbType.VarChar, 20).Value = addr.zip
        command.Parameters.Add("type", OleDbType.VarChar, 20).Value = addr.type
        Return command.ExecuteNonQuery() > 0

    End Function

    Private Function updateAddress(addr As Address) As Boolean

        Dim commandText As String = "UPDATE address set street_1 = ?, street_2 = ?, city = ?, state = ?, zip = ?, type_cd = ? where id = ?;"
        Dim command As New OleDbCommand(commandText, conn)
        command.Parameters.Add("street_1", OleDbType.VarChar, 50).Value = addr.street1
        command.Parameters.Add("street_2", OleDbType.VarChar, 50).Value = addr.street2
        command.Parameters.Add("city", OleDbType.VarChar, 20).Value = addr.city
        command.Parameters.Add("state", OleDbType.VarChar, 20).Value = addr.state
        command.Parameters.Add("zip", OleDbType.VarChar, 20).Value = addr.zip
        command.Parameters.Add("type_cd", OleDbType.VarChar, 20).Value = addr.type
        command.Parameters.Add("id", OleDbType.Numeric).Value = addr.id
        Return command.ExecuteNonQuery() > 0

    End Function

    Private Function mapDBRowToContact(row As OleDbDataReader) As Contact
        Return New Contact(row("id"), row("first_name"), row("last_name"), row("company"), row("title"), row("display_name"))
    End Function

    Private Sub populateEmails(contact As Contact)

        Dim commandText As String = "select id, address, display_name from email where contact_id = ?;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("contact_id", OleDbType.Numeric).Value = contact.id

        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        If Not reader.HasRows Then
            Return
        End If

        While reader.Read
            contact.emailList.Add(mapDBRowToEmail(reader))
        End While

    End Sub

    Private Sub populatePhones(ByRef contact As Contact)

        Dim commandText As String = "select id, area_code, prefix, line_number, type_cd from phone where contact_id = ?;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("contact_id", OleDbType.Numeric).Value = contact.id

        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        If Not reader.HasRows Then
            Return
        End If

        While reader.Read
            contact.phoneList.Add(mapDBRowToPhone(reader))
        End While
        
    End Sub

    Private Sub populateAddresses(ByRef contact As Contact)

        Dim commandText As String = "select id, street_1, street_2, city, state, zip, type_cd from address where contact_id = ?;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("contact_id", OleDbType.Numeric).Value = contact.id

        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        If Not reader.HasRows Then
            Return
        End If

        While reader.Read
            contact.addressList.Add(mapDBRowToAddress(reader))
        End While

    End Sub

    Private Function mapDBRowToPhone(row As OleDbDataReader) As PhoneNumber
        Return New PhoneNumber(row("id"), row("area_code"), row("prefix"), row("line_number"), row("type_cd"))
    End Function

    Private Function mapDBRowToEmail(row As OleDbDataReader) As Email
        Return New Email(row("id"), row("address"), row("display_name"))
    End Function

    Private Function mapDBRowToAddress(row As OleDbDataReader) As Address
        Return New Address(row("id"), row("street_1"), row("street_2"), row("city"), row("state"), row("zip"), row("type_cd"))
    End Function

    Private Sub deleteEntities(table As Tables, entityIds As String, contactId As Long)

        ' If no ids specified, use dummy id so that *all* entities are deleted.
        entityIds = If(String.IsNullOrEmpty(entityIds), "-1", entityIds)
        
        Dim commandText = "delete from " & table.ToString & " where contact_id = ? and id not in (" & entityIds & ");"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("contact_id", OleDbType.Numeric).Value = contactId
        command.ExecuteNonQuery()


    End Sub

    Private Sub deleteContactDetails(table As Tables, contactId As Long)

        Dim commandText = "delete from " & table.ToString & " where contact_id = ?;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        command.Parameters.Add("contact_id", OleDbType.Numeric).Value = contactId
        command.ExecuteNonQuery()

    End Sub

    Function getPhoneTypes() As List(Of EntityType) Implements ContactService.getPhoneTypes
        Return getEntityTypesFor(Tables.phone)
        
    End Function

    Function getAddressTypes() As List(Of EntityType) Implements ContactService.getAddressTypes
        Return getEntityTypesFor(Tables.address)
    End Function

    Private Function getEntityTypesFor(table As Tables) As List(Of EntityType)

        Dim tableName As String
        If (table = Tables.address) Then
            tableName = "address_types"
        ElseIf (table = Tables.phone) Then
            tableName = "phone_types"
        Else
            Throw New ArgumentException("Only address and phone types are valid for this method")
        End If

        Dim commandText As String = "select type_cd, type_text from " & tableName & ";"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        Dim types As List(Of EntityType) = New List(Of EntityType)
        If Not reader.HasRows Then
            Return types
        End If

        While reader.Read
            types.Add(New EntityType(reader("type_cd"), reader("type_text")))
        End While

        Return types


    End Function

    Public Function getContactList() As List(Of Contact) Implements ContactService.getContactList

        Dim commandText As String = "select id, first_name, last_name, title, company, display_name from contacts;"
        Dim command As New OleDb.OleDbCommand(commandText, conn)
        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        Dim contacts As List(Of Contact) = New List(Of Contact)
        If Not reader.HasRows Then
            Return contacts
        End If

        Dim contact As Contact
        While reader.Read
            contact = mapDBRowToContact(reader)
            populateEmails(contact)
            populatePhones(contact)
            populateAddresses(contact)
            contacts.Add(contact)
        End While
        
        Return contacts

    End Function


End Class
